package main.util;

import main.DataProcess;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 从EXCEL导入到数据库
 * @author se7en
 */
public class ObjectExcelRead {

	private String jarWholePath = DataProcess.class.getProtectionDomain().getCodeSource().getLocation()
			.getFile().substring(1).replace("dataProcess.jar","");
	private String propertiesPath = jarWholePath + File.separator + "resources/report.properties";

	public static Log log = LogFactory.getLog(DataProcess.class);

	/** 读取excel数据
	 * @param filepath 文件路径
	 * @param filename 文件名
	 * @param startRow 初始行
	 * @param startCol 初始列
	 * @return 返回读取的数据
	 * @throws Exception error
	 */
	public List<PageData> readExcel(String filepath, String filename, int startRow, int startCol) throws Exception {
		List<PageData> resultList = new ArrayList<>();
		File target = new File(filepath, filename);
		FileInputStream file = new FileInputStream(target);
		String nameString = filename.substring(filename.lastIndexOf("."));
		Workbook book = null;
		if(".xls".equals(nameString)) {
			book = new HSSFWorkbook(file);
		}else {
			book = new XSSFWorkbook(file);
		}

		for(int sheetnum=0;sheetnum<book.getNumberOfSheets();sheetnum++) {
			//sheet 从0开始
			Sheet sheet = book.getSheetAt(sheetnum);
			String sheetName=sheet.getSheetName();

			//取得最后一行的行号,行循环开始
			int rowNum = sheet.getLastRowNum() + 1;
			List<Object> varList = new ArrayList<>();
			for (int i = startRow; i < rowNum; i++) {
				PageData varpd = new PageData();
				Row row = sheet.getRow(i);
				if (row == null) {
					continue;
				}

				int cellNum = row.getLastCellNum();
				for (int j = startCol; j < cellNum; j++) {
					Cell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue;

					// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
					if (null != cell) {
						switch (cell.getCellType()) {
							case 0:
								cellValue = String.valueOf((int) cell.getNumericCellValue());
								break;
							case 1:
								cellValue = cell.getStringCellValue();
								break;
							case 2:
								cellValue = String.valueOf(cell.getDateCellValue());
								System.out.println("出错的行是："+i+",出错的列是:"+j);
								break;
							case 3:
								cellValue = "";
								break;
							case 4:
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case 5:
								cellValue = String.valueOf(cell.getErrorCellValue());
								break;
							default:
								cellValue = "";
						}
					} else {
						cellValue = "";
					}

					varpd.put("var"+j, cellValue);

				}
				varList.add(varpd);
			}
			PageData pd = new PageData();
			pd.put("sheetName", sheetName);
			pd.put("sheetData", varList);
			resultList.add(pd);
		}
		System.out.println("文件数据读取成功！");
		return resultList;
	}

	/**
	 * 带表头的Excel
	 * 宋体-10号-自动换行
	 * 数字文本转成成数字格式
	 * @param headMap 表头标题
	 * @param listData 导入的数据
	 * @param colWidth 8 * 256
	 */
	public void exportToExcel(String fileName,String[] headMap,String[] keyMap, List<PageData> listData, int colWidth) {
		// 声明一个工作薄，1000=缓存
		SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
		workbook.setCompressTempFiles(true);

		// 生成一个表格
		SXSSFSheet sheet = workbook.createSheet();

		// 设置表格默认列宽度为8个字节
		sheet.setDefaultColumnWidth((short) colWidth);

		// 内容格式
		CellStyle contentStyle =  workbook.createCellStyle();
		contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		contentStyle.setWrapText(true);
		Font contentFont = workbook.createFont();
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);
		contentStyle.setFont(contentFont);

		// 标题格式
		CellStyle titleStyle =  workbook.createCellStyle();
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		titleStyle.setWrapText(true);
		Font titleFont = workbook.createFont();
		titleFont.setFontName("宋体");
		titleFont.setBold(true);
		titleFont.setFontHeightInPoints((short) 12);
		titleStyle.setFont(titleFont);


		// 遍历集合数据，产生数据行
		int rowIndex = 0;
		for (PageData obj : listData) {
			if(rowIndex == 65535 || rowIndex == 0){
				//如果数据超过了，则在第二页显示
				if ( rowIndex != 0 ) {
					sheet = workbook.createSheet();
				}
				//列头 rowIndex =1
				SXSSFRow headerRow = sheet.createRow(0);
				for(int i=0;i<headMap.length;i++) {
					SXSSFCell newCell = headerRow.createCell(i);
					newCell.setCellStyle(titleStyle);
					newCell.setCellValue(headMap[i]);
				}
				//数据内容从 rowIndex=1开始
				rowIndex = 1;
			}

			// 数据内容
			SXSSFRow dataRow = sheet.createRow(rowIndex);
			for (int i = 0; i < headMap.length; i++) {
				SXSSFCell newCell = dataRow.createCell(i);

				Object o =  obj.get(keyMap[i]);
				String cellValue = "";
				if(o==null) {
					cellValue = "";
				} else if(o instanceof Date) {
					cellValue = new SimpleDateFormat("yyyy-MM-dd").format(o);
				} else if(o instanceof Float || o instanceof Double) {
					cellValue= new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
				} else {
					cellValue = o.toString();
				}
				newCell.setCellStyle(contentStyle);
				if (i == 5 || i == 6) {
					// 金额字段，保存类型为数字
					try {
						newCell.setCellValue(Double.parseDouble(cellValue));
					} catch (Exception e) {
						log.error("出错的行是："+rowIndex+",出错的列是:"+i);
						log.error(e);
					}
				} else {
					newCell.setCellValue(cellValue);
				}
			}
			rowIndex++;
		}

		// 自动调整宽度
////		sheet.trackAllColumnsForAutoSizing();
////		for (int i = 0; i < headMap.length; i++) {
////			sheet.autoSizeColumn(i);
////		}

		// 输出文件到指定的文件夹
		String sourceDirector = jarWholePath + PropertiesUtil.getValue(propertiesPath, "outDirector");
		try {
			String filePath = sourceDirector + File.separator + fileName + ".xlsx";
			File newFile = new File(filePath);

			FileOutputStream fos = null;
			fos = new FileOutputStream(newFile);
			workbook.write(fos);
			fos.flush();
			fos.close();

			//释放磁盘空间。处理在磁盘上支持这个工作簿的临时文件。调用该方法将使工作簿不可用。
			boolean flag =  workbook.dispose();
			//如果所有临时文件都被成功删除，则为真。
			System.out.println("输出文件：" + flag);
		} catch (IOException e) {
			e.printStackTrace();
			log.error(e);
		}
	}


}